# coding: utf-8
import sys
import os
import argparse

sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from application import app, db
from sqlalchemy import text

def delete_unmatched_user_assets_sql(merchant_id, use_charge_id_filter=True, batch_size=1000):
    """
    使用MySQL原生语句删除user_assets_options表中满足条件的数据，且在user_charge表中没有对应记录
    条件：rest_money=0 and (charge_id=1 or package_type='stored_package')
    """
    try:
        with app.app_context():
            # 构建原生SQL语句
            sql_conditions = [
                "rest_money = 0",
                f"merchant_id = {merchant_id}"
            ]

            if use_charge_id_filter:
                sql_conditions.append("charge_id = 1")

            # 构建完整的SQL删除语句
            delete_sql = text(f"""
                DELETE FROM user_assets_options
                WHERE {" AND ".join(sql_conditions)}
                AND NOT EXISTS (
                    SELECT 1
                    FROM user_charge
                    WHERE user_charge.merchant_id = :merchant_id
                    AND user_charge.user_id = user_assets_options.user_id
                )
                LIMIT :batch_size
            """)

            # 先查询匹配的记录数量，用于确认
            count_sql = text(f"""
                SELECT COUNT(*)
                FROM user_assets_options
                WHERE {" AND ".join(sql_conditions)}
                AND NOT EXISTS (
                    SELECT 1
                    FROM user_charge
                    WHERE user_charge.merchant_id = :merchant_id
                    AND user_charge.user_id = user_assets_options.user_id
                )
            """)

            # 执行计数查询
            matched_count = db.session.execute(count_sql, {"merchant_id": merchant_id}).scalar()

            if matched_count == 0:
                print("没有找到匹配的记录，无需删除。")
                return 0

            print(f"找到 {matched_count} 条匹配的记录，即将执行删除操作。")
            print(f"将使用LIMIT {batch_size} 分批删除所有记录，一次执行即可完成。")

            total_deleted = 0
            # 循环分批删除，直到删除完所有符合条件的记录
            while True:
                # 开始事务
                db.session.begin()

                # 执行删除操作，使用LIMIT限制每批删除数量
                result = db.session.execute(delete_sql, {"batch_size": batch_size, "merchant_id": merchant_id})
                deleted_count = result.rowcount

                # 提交事务
                db.session.commit()

                total_deleted += deleted_count
                print(f"已删除 {total_deleted} 条记录...")

                # 如果本次删除的记录数小于batch_size，说明已经删除完所有记录
                if deleted_count < batch_size:
                    break

            print(f"删除操作完成，共删除 {total_deleted} 条匹配的记录。")
            if total_deleted > 0:
                with open('deleted_count_log.txt', 'a', encoding='utf-8') as f:
                    f.write(f"Merchant {merchant_id}: Deleted {total_deleted} records\n")
                print(f"已将Merchant：{merchant_id}, 删除记录数保存到 deleted_count_log.txt")

            return total_deleted

    except Exception as e:
        print(f"删除过程中出现错误: {str(e)}")
        # 发生错误时回滚事务
        if 'db' in locals() and hasattr(db, 'session'):
            db.session.rollback()
        return 0


if __name__ == '__main__':
    parser = argparse.ArgumentParser(description='删除未匹配的用户资产记录')
    parser.add_argument('--merchant_id', type=int, required=True, help='要处理的商户ID')
    args = parser.parse_args()

    merchant_id = args.merchant_id

    use_charge_id_filter = True  # 设置为True表示只删除charge_id=1的记录

    # 提示用户确认操作
    print(f"警告：这是一个删除操作，将永久删除Merchant: {merchant_id} 满足条件的数据。")
    confirm = input("是否继续执行删除操作？(y/n): ").strip().lower()
    if confirm == 'y':
        deleted_count = delete_unmatched_user_assets_sql(merchant_id, use_charge_id_filter)
        print(f"操作完成，共删除 {deleted_count} 条记录。")
    else:
        print("删除操作已取消。")
